package com.peenongsaban.freelance.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.peenongsaban.freelance.bean.AptitudeBean;
import com.peenongsaban.freelance.bean.AptitudeTypeBean;
import com.peenongsaban.freelance.bean.PictureBean;
import com.peenongsaban.freelance.bean.PostJobBean;
import com.peenongsaban.freelance.bean.RequestJobBean;
import com.peenongsaban.freelance.bean.StatusOfRequestBean;
import com.peenongsaban.freelance.connection.DatabaseConnectionPool;
import com.peenongsaban.freelance.dao.JobDetailDAO;
import com.peenongsaban.freelance.meta.AptitudeMeta;
import com.peenongsaban.freelance.meta.AptitudeOfJobViewMeta;
import com.peenongsaban.freelance.meta.AptitudeTypeMeta;
import com.peenongsaban.freelance.meta.AuthorizeJobMeta;
import com.peenongsaban.freelance.meta.JobMeta;
import com.peenongsaban.freelance.meta.ListPostJobViewMeta;
import com.peenongsaban.freelance.meta.PictureOfJobViewMeta;
import com.peenongsaban.freelance.meta.PostJobMeta;
import com.peenongsaban.freelance.meta.RequestTypeMeta;
import com.peenongsaban.freelance.meta.StatusOfRequestMeta;
import com.peenongsaban.freelance.meta.UserMeta;
import com.peenongsaban.freelance.meta.ViewMeta;
import com.peenongsaban.freelance.model.AuthorizeJobBean;
import com.peenongsaban.freelance.utils.ExceptionUtil;

public class JobDetailDAOImpl implements JobDetailDAO {

	@Override
	public PostJobBean selectJobDetail(String postJobId) {
		// TODO Night Mock up
		PostJobBean bean = new PostJobBean();
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		String sql = "";
		try {
			// Query job detail
			sql = "SELECT * FROM " + ListPostJobViewMeta.VIEW_CONDITON
					+ " WHERE " + ListPostJobViewMeta.COL_POST_ID + " = ?";
			statement = conn.prepareStatement(sql);
			statement.setString(1, postJobId);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {

				bean.setPostID(rs.getString(ListPostJobViewMeta.COL_POST_ID));
				bean.setEndDateOfPost(rs
						.getDate(PostJobMeta.COL_END_DATE_OF_POST));
				bean.getJob().setJobID(rs.getString(JobMeta.COL_JOB_ID));
				bean.getUser().setIdCard(
						rs.getString(ListPostJobViewMeta.COL_ID_CARD));
				bean.getUser().setTrustLevel(
						rs.getDouble(ListPostJobViewMeta.COL_TRUST_LEVEL));
				bean.setPostDate(rs.getDate(ListPostJobViewMeta.COL_POST_DATE));
				bean.getJob().setJobName(
						rs.getString(ListPostJobViewMeta.COL_JOB_NAME));
				bean.setStatusOfPost(rs
						.getString(ListPostJobViewMeta.COL_STATUS_OF_POST));
				bean.getJob()
						.setDescriptionOfJob(
								rs.getString(ListPostJobViewMeta.COL_DESCRIPTION_OF_JOB));
				bean.getJob().setFormatTime(
						rs.getString(ListPostJobViewMeta.COL_FORMAT_TIME));
				bean.getJob().setWorkArea(
						rs.getString(ListPostJobViewMeta.COL_WORK_AREA));
				bean.getJob().setStartDateOfJob(
						rs.getDate(ListPostJobViewMeta.COL_START_DATE_OF_JOB));
				bean.getJob().setEndDateOfJob(
						rs.getDate(ListPostJobViewMeta.COL_END_DATE_OF_JOB));
				bean.getJob()
						.getPaymentOfJob()
						.setFixPrice(
								rs.getDouble(ListPostJobViewMeta.COL_FIX_PRICE));
				bean.getJob()
						.getPaymentOfJob()
						.setMinPrice(
								rs.getDouble(ListPostJobViewMeta.COL_MIN_PRICE));
				bean.getJob()
						.getPaymentOfJob()
						.setMaxPrice(
								rs.getDouble(ListPostJobViewMeta.COL_MAX_PRICE));
				bean.setAmountRequestPost(rs
						.getInt(ListPostJobViewMeta.COL_AMOUNT_USER_REQUEST_JOB));
			}

			// Query aptitude of job
			Vector<AptitudeBean> apVector = new Vector<AptitudeBean>();
			statement = null;
			sql = "SELECT * FROM " + AptitudeOfJobViewMeta.VIEW + " WHERE "
					+ AptitudeOfJobViewMeta.COL_POST_ID + " = ?";
			statement = conn.prepareStatement(sql);
			statement.setString(1, bean.getPostID());
			rs = statement.executeQuery();

			while (rs.next()) {
				AptitudeBean ap = new AptitudeBean();
				ap.setAptitudeID(Integer.parseInt(rs
						.getString(AptitudeMeta.COL_APTITUDE_ID)));
				ap.setAptitudeName(rs
						.getString(AptitudeMeta.COL_APTITUDE_NAME));
				ap.setDescription(rs
						.getString(AptitudeMeta.COL_DESCRIPTION));
				ap.getAptitudeType().setAptitudeTypeID(
						rs.getInt(AptitudeMeta.COL_APTITUDE_ID));
				ap.getAptitudeType()
				.setAptitudeTypeID(
						rs.getInt(AptitudeTypeMeta.COL_APTITUDE_TYPE_ID));
				ap.getAptitudeType()
						.setAptitudeTypeName(
								rs.getString(AptitudeTypeMeta.COL_APTITUDE_TYPE_NAME));
				apVector.add(ap);
			}
			bean.setListAptitude(apVector);

			// Query picture of job
			Vector<PictureBean> picVector = new Vector<PictureBean>();
			statement = null;
			sql = "SELECT * FROM " + PictureOfJobViewMeta.VIEW + " WHERE "
					+ PictureOfJobViewMeta.COL_JOB_ID + " = ?";
			statement = conn.prepareStatement(sql);
			statement.setString(1, bean.getJob().getJobID());
			rs = statement.executeQuery();
			while (rs.next()) {
				PictureBean pic = new PictureBean();
				pic.setPictureID(rs
						.getString(PictureOfJobViewMeta.COL_PICTURE_ID));
				pic.setPictureLocation(rs
						.getString(PictureOfJobViewMeta.COL_PICTURE_LOCATION));
				pic.setDescription(rs
						.getString(PictureOfJobViewMeta.COL_DESCRIPTION));
				picVector.add(pic);
			}
			bean.getJob().setListPicture(picVector);

			// Query person post this job
			statement = null;
			sql = "SELECT * FROM " + UserMeta.TABLE + " WHERE "
					+ UserMeta.COL_ID_CARD + " = ?";
			statement = conn.prepareStatement(sql);
			statement.setString(1, bean.getUser().getIdCard());
			rs = statement.executeQuery();
			while (rs.next()) {
				bean.getUser().setName_th(rs.getString(UserMeta.COL_NAME_TH));
				bean.getUser().setLastName_th(
						rs.getString(UserMeta.COL_LASTNAME_TH));
				bean.getUser().setName_en(rs.getString(UserMeta.COL_NAME_EN));
				bean.getUser().setLastName_en(
						rs.getString(UserMeta.COL_LASTNAME_EN));
				bean.getUser().setGender(rs.getString(UserMeta.COL_GENDER));
				if (rs.getString(UserMeta.COL_PHONE) != null) {
					if (rs.getString(UserMeta.COL_PHONE).length() != 0) {
						bean.getUser().setPhone(
								rs.getString(UserMeta.COL_PHONE).split(","));
					}
				}
				bean.getUser().setFax(rs.getString(UserMeta.COL_FAX));
				if (rs.getString(UserMeta.COL_EMAIL) != null) {
					if (rs.getString(UserMeta.COL_EMAIL).length() != 0) {
						bean.getUser().setEmail(
								rs.getString(UserMeta.COL_EMAIL).split(","));
					}
				}
				bean.getUser().setFacebook(rs.getString(UserMeta.COL_FACEBOOK));
				bean.getUser().setLine(rs.getString(UserMeta.COL_LINE));
				bean.getUser().setTwitter(rs.getString(UserMeta.COL_TWITTER));
				if (rs.getString(UserMeta.COL_WEBSITE) != null) {
					if (rs.getString(UserMeta.COL_WEBSITE).length() != 0) {
						bean.getUser().setWebsite(
								rs.getString(UserMeta.COL_WEBSITE).split(","));
					}
				}
				bean.getUser().setPicture(rs.getString(UserMeta.COL_PICTURE));
				bean.getUser().setExperienceOther(
						rs.getString(UserMeta.COL_EXPERIENCE_OTHER));
				bean.getUser().setUserStatus(
						rs.getString(UserMeta.COL_USER_STATUS));
				bean.getUser().setIntroduce(
						rs.getString(UserMeta.COL_INTRODUCE));
				bean.getUser().setAlias(rs.getString(UserMeta.COL_ALIAS));
			}
			bean.getJob().setListPicture(picVector);

			// Query person request this job
			Vector<RequestJobBean> reqVector = new Vector<RequestJobBean>();
			statement = null;
			sql = "SELECT * FROM " + ViewMeta.USER_REQUEST_JOB_V + " WHERE "
					+ PostJobMeta.COL_POST_ID + " = ? ";
			statement = conn.prepareStatement(sql);
			statement.setString(1, bean.getPostID());
			rs = statement.executeQuery();
			while (rs.next()) {
				RequestJobBean reqBean = new RequestJobBean();
				reqBean.getUser().setIdCard(rs.getString(UserMeta.COL_ID_CARD));
				reqBean.getUser().setAlias(rs.getString(UserMeta.COL_ALIAS));
				reqBean.getUser().setTrustLevel(
						rs.getDouble(UserMeta.COL_TRUST_LEVEL));
				Vector<StatusOfRequestBean> statusVec = new Vector<StatusOfRequestBean>();
				StatusOfRequestBean statusBean = new StatusOfRequestBean();
				statusBean.setDateTime(rs
						.getDate(StatusOfRequestMeta.COL_DATE_TIME));
				statusBean.setPriceOfBid(rs
						.getDouble(StatusOfRequestMeta.COL_PRICE_OF_BID));
				statusVec.add(statusBean);
				reqBean.setListStatusOfRequest(statusVec);
				reqVector.add(reqBean);
			}
			bean.setListRequestJob(reqVector);

		} catch (Exception e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			databaseConnection.destroy();
			databaseConnection = null;
			try {
				statement.close();
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return bean;
	}

	@Override
	public Vector<AptitudeTypeBean> listCategoryJob() {
		Vector<AptitudeTypeBean> vector = new Vector<AptitudeTypeBean>();
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT J." + AptitudeTypeMeta.COL_APTITUDE_TYPE_ID
					+ ",J." + AptitudeTypeMeta.COL_APTITUDE_TYPE_NAME + ",J."
					+ AptitudeTypeMeta.COL_IMAGE_ICON + ",L.AMOUNT FROM "
					+ AptitudeTypeMeta.TABLE + " J LEFT JOIN (SELECT "
					+ ListPostJobViewMeta.COL_APTITUDE_TYPE_ID + ","
					+ ListPostJobViewMeta.COL_APTITUDE_TYPE_NAME + ","
					+ "COUNT(*) AS AMOUNT FROM "
					+ ListPostJobViewMeta.VIEW_CATEGORYTYPE + " GROUP BY "
					+ ListPostJobViewMeta.COL_APTITUDE_TYPE_ID + ") L ON J."
					+ AptitudeTypeMeta.COL_APTITUDE_TYPE_ID + " = L."
					+ ListPostJobViewMeta.COL_APTITUDE_TYPE_ID;

			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				AptitudeTypeBean bean = new AptitudeTypeBean();
				bean.setAptitudeTypeID(rs
						.getInt(AptitudeTypeMeta.COL_APTITUDE_TYPE_ID));
				bean.setAptitudeTypeName(rs
						.getString(AptitudeTypeMeta.COL_APTITUDE_TYPE_NAME));
				bean.setImage_icon(rs
						.getString(AptitudeTypeMeta.COL_IMAGE_ICON));
				bean.setCountSumJob(rs.getInt("AMOUNT"));
				vector.add(bean);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

	@Override
	public Vector<PostJobBean> listLastPostJob() {
		Vector<PostJobBean> vector = new Vector<PostJobBean>();
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT " + ListPostJobViewMeta.COL_POST_ID + ","
					+ ListPostJobViewMeta.COL_JOB_NAME + " FROM "
					+ ListPostJobViewMeta.VIEW_CONDITON + " ORDER BY "
					+ ListPostJobViewMeta.COL_POST_DATE + " DESC LIMIT 0,9";

			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				PostJobBean bean = new PostJobBean();
				bean.setPostID(rs.getString(ListPostJobViewMeta.COL_POST_ID));
				bean.getJob().setJobName(
						rs.getString(ListPostJobViewMeta.COL_JOB_NAME));
				vector.add(bean);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

	@Override
	public AuthorizeJobBean getAuthorizeJob(String idCard, String postId) {
		AuthorizeJobBean bean = new AuthorizeJobBean();
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT * FROM "
					+ AuthorizeJobMeta.TABLE + " WHERE "+PostJobMeta.COL_POST_ID+" = ? AND "+UserMeta.COL_ID_CARD+" = ? ";

			statement = conn.prepareStatement(sql);
			statement.setString(1, postId);
			statement.setString(2, idCard);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				bean.setPostID(rs.getString(PostJobMeta.COL_POST_ID));
				bean.setIdCard(rs.getString(UserMeta.COL_ID_CARD));
				bean.setStatusAuthorizeJob(rs.getString(AuthorizeJobMeta.COL_STATUS_AUTHORIZE));
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return bean;
	}

	@Override
	public Vector<RequestJobBean> getUserRequestJobDetail(String postID,int statusId) {
		Vector<RequestJobBean> vector = new Vector<RequestJobBean>();
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		String sql;
		try {
			sql = "SELECT * FROM " + ViewMeta.USER_REQUEST_JOB_V + " WHERE "
					+ PostJobMeta.COL_POST_ID + " = ? AND "+RequestTypeMeta.COL_REQUEST_TYPE_ID+" = ? ";
			statement = conn.prepareStatement(sql);
			statement.setString(1, postID);
			statement.setInt(2, statusId);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				RequestJobBean reqBean = new RequestJobBean();
				reqBean.getUser().setIdCard(rs.getString(UserMeta.COL_ID_CARD));
				reqBean.getUser().setAlias(rs.getString(UserMeta.COL_ALIAS));
				reqBean.getUser().setTrustLevel(rs.getDouble(UserMeta.COL_TRUST_LEVEL));
				Vector<StatusOfRequestBean> statusVec = new Vector<StatusOfRequestBean>();
				StatusOfRequestBean statusBean = new StatusOfRequestBean();
				statusBean.setDateTime(rs.getDate(StatusOfRequestMeta.COL_DATE_TIME));
				statusBean.setPriceOfBid(rs.getDouble(StatusOfRequestMeta.COL_PRICE_OF_BID));
				statusVec.add(statusBean);
				reqBean.setListStatusOfRequest(statusVec);
				vector.add(reqBean);
			}		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

	@Override
	public RequestJobBean requestJobDetail(String idCard,String postId){

		RequestJobBean reqBean = new RequestJobBean();
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		String sql;
		try {
			sql = "SELECT * FROM " + ViewMeta.LIST_MY_REQUEST_JOB_V + " WHERE "
					+ PostJobMeta.COL_POST_ID + " = ? AND "+UserMeta.COL_ID_CARD+" = ? ";
			statement = conn.prepareStatement(sql);
			statement.setString(1, postId);
			statement.setString(2, idCard);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				Vector<StatusOfRequestBean> statusVec = new Vector<StatusOfRequestBean>();
				StatusOfRequestBean statusBean = new StatusOfRequestBean();
				statusBean.setDateTime(rs.getDate(StatusOfRequestMeta.COL_DATE_TIME));
				statusBean.setPriceOfBid(rs.getDouble(StatusOfRequestMeta.COL_PRICE_OF_BID));
				statusBean.getRequestType().setRequestTypeID(rs.getInt(RequestTypeMeta.COL_REQUEST_TYPE_ID));
				statusBean.getRequestType().setRequestTypeName(rs.getString(RequestTypeMeta.COL_REQUEST_TYPE_NAME));
				statusVec.add(statusBean);
				reqBean.setListStatusOfRequest(statusVec);
			}		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return reqBean;
	
	}
}
